﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;
using ZWL.DBUtility;//请先添加引用

namespace ZWL.BLL
{
   public  class StuffAliasLibrary
    {
       public StuffAliasLibrary() { }
       #region Model
       private int _id;
       private string _StuffAlias;
       private string _StuffNameModel;
       private string _StuffCode;
     
       /// <summary>
       /// 
       /// </summary>
       public int ID
       {
           set { _id = value; }
           get { return _id; }
       }

       /// <summary>
       /// 别名
       /// </summary>
       public string StuffAlias
       {
           set { _StuffAlias = value; }
           get { return _StuffAlias; }
       }
       /// <summary>
       /// 标准名
       /// </summary>
       public string StuffNameModel
       {
           set { _StuffNameModel = value; }
           get { return _StuffNameModel; }
       }
       /// <summary>
       /// 物资编码
       /// </summary>
       public string StuffCode
       {
           set { _StuffCode = value; }
           get { return _StuffCode; }
       }
       
       #endregion Model
        #region  成员方法

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public StuffAliasLibrary(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,StuffAlias,StuffNameModel,StuffCode ");
            strSql.Append(" FROM StuffAliasLibrary ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                StuffAlias = ds.Tables[0].Rows[0]["StuffAlias"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffAlias"].ToString() : "";
                StuffNameModel = ds.Tables[0].Rows[0]["StuffNameModel"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffNameModel"].ToString() : "";
                StuffCode = ds.Tables[0].Rows[0]["StuffCode"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffCode"].ToString() : "";
               

            }
        }

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ID", "StuffAliasLibrary");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from StuffAliasLibrary");
            strSql.Append(" where ID=@ID ");

            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into StuffAliasLibrary(");
            strSql.Append("StuffAlias,StuffNameModel,StuffCode)");
            strSql.Append(" values (");
            strSql.Append("@StuffAlias,@StuffNameModel,@StuffCode)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@StuffAlias", SqlDbType.VarChar,100),
					new SqlParameter("@StuffNameModel", SqlDbType.VarChar,100),
				    new SqlParameter("@StuffCode", SqlDbType.VarChar,50)};
            parameters[0].Value = StuffAlias;
            parameters[1].Value = StuffNameModel;
            parameters[2].Value = StuffCode;
          

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update StuffAliasLibrary set ");
            strSql.Append("StuffAlias=@StuffAlias,");
            strSql.Append("StuffNameModel=@StuffNameModel,");
            strSql.Append("StuffCode=@StuffCode ");
           
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@StuffAlias", SqlDbType.VarChar,100),
					new SqlParameter("@StuffNameModel", SqlDbType.VarChar,100),
				    new SqlParameter("@StuffCode", SqlDbType.VarChar,50)};
            parameters[0].Value = ID;
            parameters[1].Value = StuffAlias;
            parameters[2].Value = StuffNameModel;
            parameters[3].Value = StuffCode;
          

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary> 
        /// 删除一条数据
        /// </summary>
        public void Delete(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from StuffAliasLibrary ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public void GetModel(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,StuffAlias,StuffNameModel,StuffCode");
            strSql.Append(" FROM StuffAliasLibrary ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                StuffAlias = ds.Tables[0].Rows[0]["StuffAlias"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffAlias"].ToString() : "";
                StuffNameModel = ds.Tables[0].Rows[0]["StuffNameModel"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffNameModel"].ToString() : "";
                StuffCode = ds.Tables[0].Rows[0]["StuffCode"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffCode"].ToString() : "";
               
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM StuffAliasLibrary ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// Excel数据导入Datable
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)
        {
            //office2007之前 仅支持.xls
            //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            System.Data.DataTable dt = null;
            //建立连接
            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //获取Excel的第一个Sheet名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
                //查询sheet中的数据
                string strSql = "select * from [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// SqlBulkCopy导入数据到数据库
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public void SqlBulkCopyInsetData(System.Data.DataTable dt)
        {


            //数据库连接
            string strConnection = ConfigurationManager.ConnectionStrings["BDOAConnectionString"].ToString();
            SqlConnection sqlConnection = new SqlConnection(strConnection);
            sqlConnection.Open();

            //声明SqlBulkCopy ,using释放非托管资源
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(strConnection))
            {
                //一次批量的插入的数据量
                sqlBC.BatchSize = 1000;
                //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                sqlBC.BulkCopyTimeout = 60;

                //设置要批量写入的表
                sqlBC.DestinationTableName = "StuffAliasLibrary";

                //自定义的datatable和数据库的字段进行对应
                sqlBC.ColumnMappings.Add("别名", "StuffAlias");
                sqlBC.ColumnMappings.Add("标准名", "StuffNameModel");
                sqlBC.ColumnMappings.Add("物资编码", "StuffCode");

                //批量写入
                sqlBC.WriteToServer(dt);
            }
            sqlConnection.Dispose();

        }



        #endregion  成员方法

        
    }
}
